1. Field of the Invention
This invention relates in general to computer-implemented database systems, and, in particular, to a technique for increasing the efficiency of executing multiple application processes when the application processes require access to the same meta data or definitional data such as in a database catalog table.
2. Description of Related Art
Databases are computerized information storage and retrieval systems. A Relational Database Management System (RDBMS) is a database management system (DBMS) which uses relational techniques for storing and retrieving data. Relational databases are organized into tables which consist of rows and columns of data. The rows are formally called tuples. A database will typically have many tables and each table will typically have multiple tuples and multiple columns. The tables are typically stored on direct access storage devices (DASD) such as magnetic or optical disk drives for semi-permanent storage.
RDBMS software using a Structured Query Language (SQL) interface is well known in the art. The SQL interface has evolved into a standard language for RDBMS software and has been adopted as such by both the American National Standards Institute (ANSI) and the International Standards Organization (ISO). The SQL interface allows users to formulate relational operations on the tables either interactively, in batch files, or embedded in host languages to manipulate the data.
SQL has three components which enable databases to be created and manipulated. One of these components is the Data Definition Language (DDL). DDL provides the language to define objects which make up the database. In other words, DDL provides the ability to create, change, and delete objects including, for example, a table with corresponding SQL statements such as CREATE, ALTER and DROP statements, or other similar statements that perform these functions.
The definitions for SQL provide that a RDBMS should respond to a particular query with a particular set of data given a specified database content, but the technique that the RDBMS uses to actually find the required information in the tables on the disk drives is left up to the RDBMS. Typically, there will be more than one technique that can be used by the RDBMS to access required data. The RDBMS will optimize the technique used to find the data requested in a query in order to minimize the computer time used and, therefore, the cost of doing the query.
Within relational databases, such as the DB2® Database Management System from International Business Machines, Corporation, there are databases which may be accessed and modified by end users and databases which are system databases. System databases may or may not be available to a user. A catalog is an example of a system database. A catalog is a collection of tables that contains descriptions and definitions of these objects (e.g., tables, views and indexes). These objects may be system defined and/or user defined. Each catalog table may serve as a directory for the location of objects and related data which is needed by application processes or transactions (i.e., units of work; an application process may invoke multiple transactions). A catalog may include one or more catalog tables, and each catalog table may include descriptions of both system objects and user-defined objects. Thus, catalog tables of the catalog may be accessed by both the system and end users depending on particular needs.
When, for example, an application process or a transaction requests a table holding data (i.e., a data table) that is to be accessed by the application process or transaction, the transaction may need to refer to a catalog table for information relating to that particular data table. In order to access the contents of the catalog table, the transaction obtains a lock on the catalog table. Locks may be non-exclusive or exclusive and are used to maintain integrity of data within catalog tables, as well as, the data table. In other words, the catalog table may be “checked out” by the transaction. When a transaction is creating/defining, changing, or deleting objects, the transaction, through a DDL statement, may place an exclusive lock on the catalog table, as well as the object, such that other transactions can not access or utilize the catalog table, the object, or related object information.
A non-exclusive lock may be utilized when, for example, a first transaction accesses the catalog table to read data about a table or table data. In this case, the contents of the catalog table are not being changed, and therefore, a second transaction may be permitted to read data from the catalog table and/or the data table, but neither transaction may record new data to the catalog table or possibly the data table. Thus, non-exclusive locks may permit multiple transactions to read data from a catalog table. However, non-exclusive locks are not typically used when transactions create, delete, or alter the description or contents of a catalog table or data tables and their data such that the changes are intended to be permanent.
An exclusive lock may be used when, for example, a first transaction accesses a catalog table to create/define, change, or delete a data table, and the first transaction must record new information back to the catalog table. In this instance, the exclusive lock may prevent other transactions from accessing the catalog table, as well as, the data table. As a result, only the first transaction may access the catalog table and possibly the data table, and all other subsequent transactions must wait to access the catalog table and data table, even for non-exclusive access, until after the first transaction has completed its use of the corresponding table, data table, or related data. Thus, placing an exclusive lock on the catalog table ensures that other transactions do not attempt to update the catalog table, data table, or related data at the same time as the first transaction. Further, exclusive locks ensure that subsequent transactions will access the most recent or updated data provided by the first transaction.
In conventional systems, when a second transaction requests exclusive or nonexclusive access to the catalog table, data table, or related data exclusively locked by the first transaction, lock contention occurs. In other words, the second transaction must wait until the first transaction finishes using the catalog table, data table, or any related tables and table data after which time the locks may be released. When the locks are released, the second transaction may access the catalog table to locate a data table or related information. As a result, each transaction that must access that catalog table and/or data table following a prior transaction that is exclusively utilizing that catalog table and/or data table must wait until the prior transaction no longer requires such exclusive use. Consequently, transactions require more time to process, processing costs are increased, and system performance is diminished.
Thus, there is a need in the art for a technique that enables concurrent transactions to utilize the same catalog table to obtain, create/define, change, or delete a private and/or non-private object and/or its data such that transactions, processing times, and processing costs are minimized.